package com.pingan.cn.crud;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class CrudServiceImpl implements CrudService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> executeQuery(String sql, Object[] params) {
        return jdbcTemplate.queryForList(sql, params);
    }

    @Override
    public int executeUpdate(String sql, Object[] params) {
        return jdbcTemplate.update(sql, params);
    }

    // 其他增删改查的方法实现...

    @Override
    public void uploadExcelFile2db(MultipartFile file, String sql) throws IOException {
        try  {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet1 = workbook.getSheetAt(0);
            List<Object[]> sheet1DataList = processSheet(sheet1);

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Object[] params = sheet1DataList.get(i);
                    for (int j = 0; j < params.length; j++) {
                        ps.setObject(j + 1, params[j], Types.VARCHAR);
                    }
                }

                @Override
                public int getBatchSize() {
                    return sheet1DataList.size();
                }
            });

        } catch (IOException e) {
            System.out.println(e);
            throw e;
        } catch (Exception e) {
            System.out.println(e);
            throw e;
        }
    }

    private List<Object[]> processSheet(Sheet sheet) {
        List<Object[]> dataList = new ArrayList<>();
        Row headerRow = sheet.getRow(0);
        int numColumns = headerRow.getPhysicalNumberOfCells();

        for (Row row : sheet) {
            if (row.getRowNum() == 0) continue; // Skip header row
            Object[] rowData = new Object[numColumns];
            for (int i = 0; i < numColumns; i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            rowData[i] = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            rowData[i] = cell.getNumericCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            rowData[i] = cell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            rowData[i] = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            rowData[i] = null;
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            rowData[i] = cell.getErrorCellValue();
                            break;
                        default:
                            rowData[i] = null;
                    }
                } else {
                    rowData[i] = null;
                }
            }
            dataList.add(rowData);
        }
        return dataList;
    }
}
